Re: Take your postgresSql on the road, and live to tell of it.

At 5:13 PM -0500 8/7/07, Scott Marlowe wrote:
>On 8/7/07, Owen Hartnett <owen@clipboardinc.com> wrote:
>>
>>  Here's what I want to do:
>>
>>  Checkpoint the database in whatever way is appropriate.
>>
>>  Make copies of the database on several laptops for use in the field
>>  (in automobiles) to do database changes.  Record all the changes made
>>  since the checkpoint as the user makes them.
>>
>>  Periodically take all the changes back into the office, take the
>>  changes made out in the field and apply them to the main database.
>>
>>  Repeat the process.
>>
>>  Notes:
>>
>>  1) Unless an user makes a mistake, there should be no changes to the
>>  same records by multiple users.  (i.e. any concurrency violations
>>  should be registered as an exception.)
>>
>>  2) I'd prefer it to just record the sql commands executed by the
>>  database as text, then use psql < myFieldcommands to update the
>>  database.  This will also help me isolate any concurrency exceptions,
>>  and I'd like to wrap the whole update in a transaction, so I can roll
>>  the whole thing back if it does detect concurrency problems anywhere
>>  in the process (then I can edit out the offending lines).
>>
>>  3) There's no particular rush to update the database - I don't need
>>  this real-time.
>>
>>  4) Users might make their checkpoint at a different time from other users.
>
>Given that each person is likely to only be only operating on their
>own data set, I'd use an integer range for each person.  Make an int
>field in each table, and give each use a 1,000,000 id range to play
>in, or something like that.  You can even set it up so that the app
>uses sequences and have them start at whatever the user's first id is,
>and not cycling and stopping when it reaches the end to keep them from
>bumping into the next person's range.
>
>Heck, go with bigint and give each person a 1,000,000,000 range.  Then
>you could still handle 9,223,372,035 or so users before you'd run out
>of sequences for each.
>
>Heck, you could even write a system of update functions that checked
>the userid against their numeric range and only updated the data if it
>was in their range.  Send it to a coworker for approval if it's not.
>I'm having a few too mad scientist moments right about now.  Got to
>get back to my data mining project...

This would probably work, but it seems like overkill...I'll have to
think about it some more...

-Owen